package daoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.*;

import dao_interface.StudentDao;
import db.DbConnection;
import po.Room;
import po.Student;

public class SDaoImpl implements StudentDao{

	/**
	 *增加学生信息方法
	 */
	@Override
	public void save(Student student) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		int result = 0;
		conn= DbConnection.getConnection();
        try {
        	sql = "INSERT INTO student VALUES(?,?,?,?,?,?,?,?)";
	        ps = conn.prepareStatement(sql);
	        ps.setString(1, student.getName());
	        ps.setLong(2, student.getStudentId());
	        ps.setString(3, student.getRoom().getRoom_name());
	        ps.setDate(4, new java.sql.Date(student.getBirthday().getTime()));
	        ps.setString(5, student.getPhoneNumber());
	        ps.setString(6, student.getEmail());
	        ps.setString(7, student.getAddress());
	        ps.setString(8, student.getNative_place());
	        result = ps.executeUpdate();//executeUpdate()方法返回int值，值为影响的行数，增删改使用。
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
	        DbConnection.close(rs, ps);
	        System.out.println("操作结果："+result);
	}

	/**
	 *删除学生信息方法
	 */
	@Override
	public void delete(long id) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = 0;
		String sql = null;
		conn= DbConnection.getConnection();
        try {
        	sql = "delete from student where 学号  = ?";
			ps = conn.prepareStatement(sql);
	        ps.setLong(1, id);
	        result = ps.executeUpdate();
        }catch(Exception e) {
        	e.printStackTrace();
        }
        DbConnection.close(rs, ps);
	}

	/**
	 *修正学生信息方法
	 */
	@Override
	public void update(Student student) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		java.util.Date date = student.getBirthday();
		int result = 0;
		conn = DbConnection.getConnection();
        try {
        	sql = "UPDATE student SET 姓名=?,生日=?,手机号码=?,邮箱=?,老家=?,住址=?,宿舍名=? where 学号 = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getName());
			ps.setDate(2, new java.sql.Date(date.getTime()));
	        ps.setString(3, student.getPhoneNumber());
	        ps.setString(4, student.getEmail());
	        ps.setString(5, student.getNative_place());
	        ps.setString(6, student.getAddress());
	        ps.setString(7, student.getRoom().getRoom_name());
	        ps.setLong(8, student.getStudentId());
	        result = ps.executeUpdate();
        }
        catch (Exception e) {
        	e.printStackTrace();
        }
        DbConnection.close(rs, ps);
		
	}

	/**
	 *查询单个学生
	 */
	@Override
	public Student get(Long id) {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		Student student = null;
		conn = DbConnection.getConnection();
		try {
			sql = "SELECT * FROM student WHERE 学号 = ?";
			ps = conn.prepareStatement(sql);
			ps.setLong(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(1);
				Long student_id = rs.getLong(2);
				String roomName = rs.getString(3);
				Date date = rs.getDate(4);
				String phoneNum = rs.getString(5);
				String email = rs.getString(6);
				String address = rs.getString(7);
				String native_place = rs.getString(8);
				Room room = new Room(roomName);
				student = new Student(name, student_id, date, phoneNum, email, native_place, address, room);
				student.toString();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return student;
	}
	
	/**
	 *查询所有学生
	 */
	@Override
	public List<Student> findAll() {
		Connection conn=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		List<Student> studentList = new ArrayList<Student>();
		conn = DbConnection.getConnection();
		try {
			sql = "SELECT * FROM student";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString(1);
				Long student_id = rs.getLong(2);
				String roomName = rs.getString(3);
				Date date = rs.getDate(4);
				String phoneNum = rs.getString(5);
				String email = rs.getString(6);
				String address = rs.getString(7);
				String native_place = rs.getString(8);
				Room room = new Room();
				room.setRoom_name(roomName);
				Student student = new Student(name, student_id, date, phoneNum, email, native_place, address, room);
				studentList.add(student);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DbConnection.close(rs,ps);
		}
		return studentList;
	}

	/**
	 *通过学生学号查询学生
	 */
	@Override
	public List<Student> getByStudentName(String name) {
		List<Student> list = new ArrayList<Student>();
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT * FROM student WHERE 姓名 = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while(rs.next()) {
				String student_name = rs.getString(1);
				Long student_id = rs.getLong(2);
				String roomName = rs.getString(3);
				Date date = rs.getDate(4);
				String phoneNum = rs.getString(5);
				String email = rs.getString(6);
				String address = rs.getString(7);
				String native_place = rs.getString(8);
				Room room = new Room();
				room.setRoom_name(roomName);
				Student student = new Student(student_name, student_id, date, phoneNum, email, native_place, address, room);
				list.add(student);
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}

}
